﻿using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Dapper;
using System.Threading.Tasks;
using System.Data.Common;

namespace DAL
{
    public class BiWarehouseDal
    {
        private readonly AppDbContext _dbContext;

        public BiWarehouseDal(AppDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="data">实体</param>
        /// <returns>结果</returns>
        public async Task<int> CreateAsync(BiWarehouse data)
        {
            // 新增实体到数据库集
            _dbContext.BiWarehouse.Add(data);
            // 保存更改
            return await _dbContext.SaveChangesAsync() ;
        }

        


        /// <summary>
        /// 根据ID查询
        /// </summary>
        /// <param name="Id">ID</param>
        /// <returns>用户列表</returns>
        public async Task<BiWarehouse> GetByIdAsync(int Id)
        {
            // 根据ID查询
            return await _dbContext.BiWarehouse.FirstOrDefaultAsync(g => g.WarehouseId == Id);
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns>用户列表</returns>
        public async Task<List<BiWarehouse>> GetAllAsync()
        {
            // 查询所有并转换为列表
            return await _dbContext.BiWarehouse.ToListAsync();
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="date">包含更新信息</param>
        public async Task<int> UpdateAsync(BiWarehouse date)
        {
            // 标记实体为修改状态
            _dbContext.BiWarehouse.Update(date);
            // 保存更改
            return await _dbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 库位与仓库主管（用户表）两表联查
        /// 等价于SQL：SELECT storage_location_name,users_name FROM dbo.wh_location INNER JOIN dbo.sys_users ON StorageLocationId= sys_users.users_id
        /// </summary>
        /// <returns>包含库位名称和主管用户名的列表</returns>
        public async Task<List<dynamic>> GetLocationWithUserAsync()
        {
            var sql = @"
SELECT 
    l.StorageLocationCode,
    l.storage_location_name,
    l.storage_location_capacity,
    -- 关联库位自身的主管：wh_location.manager_id → sys_users.users_id
    u.users_name AS warehouse_manager_name,
    l.sort_order,
    TRIM(l.storage_location_status) AS storage_location_status,
    l.remarks
FROM dbo.wh_location l
-- 直接通过库位表的manager_id关联用户表获取主管名称
INNER JOIN dbo.sys_users u ON l.manager_id = u.users_id;";

            var connectionString = _dbContext.Database.GetConnectionString();
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                var result = await connection.QueryAsync<dynamic>(sql);
                return result.AsList();
            }
        }

        /// <summary>
        /// 获取所有库位对应的主管姓名（去重）
        /// </summary>
        /// <returns>库位主管姓名列表</returns>
        public async Task<List<dynamic>> GetUniqueLocationManagersAsync()
        {
            // SQL查询：获取所有不重复的库位主管姓名
            var querySql = @"
SELECT DISTINCT
    u.users_id AS UserId,  -- 用户唯一ID
    u.users_name AS LocationManagerName  -- 主管姓名
FROM dbo.wh_location l
INNER JOIN dbo.sys_users u 
    ON l. StorageLocationId= u.users_id;";

            // 获取数据库连接字符串
            var dbConnectionString = _dbContext.Database.GetConnectionString();

            // 执行查询并返回结果
            using (DbConnection dbConnection = new SqlConnection(dbConnectionString))
            {
                await dbConnection.OpenAsync();
                var managers = await dbConnection.QueryAsync<dynamic>(querySql);
                return managers.AsList();
            }
        }

        /// <summary>
        /// 根据ID删除
        /// </summary>
        /// <param name="Id">ID</param>
        public async Task<int> DeleteAsync(int Id)
        {
            // 先查询实体
            var data = await GetByIdAsync(Id);
            if (data != null)
            {
                // 移除实体
                _dbContext.BiWarehouse.Remove(data);
                // 保存更改
                return await _dbContext.SaveChangesAsync();
            }
            return 0;
        }

        /// <summary>
        /// 新增库位
        /// </summary>
        /// <param name="data">实体</param>
        /// <returns>结果</returns>
        public async Task<int> LocationAddAsync(WhLocation data)
        {
            // 新增实体到数据库集
            _dbContext.WhLocation.Add(data);
            // 保存更改
            return await _dbContext.SaveChangesAsync();
        }


        /// <summary>
        /// 通过过仓库编号查询对应的仓库主键ID
        /// </summary>
        /// <param name="warehouseCode">仓库编号</param>
        /// <returns>仓库主键ID（不存在时返回0或抛出异常）</returns>
        public async Task<long> GetWarehouseIdByCodeAsyncsync(string warehouseCode)
        {
            // 校验入参
            if (string.IsNullOrWhiteSpace(warehouseCode))
            {
                throw new ArgumentException("仓库编号不能为空", nameof(warehouseCode));
            }

            // 查询仓库（假设仓库表实体为BiWarehouse，主键为long类型的WarehouseId）
            var warehouse = await _dbContext.BiWarehouse
                .Where(w => w.WarehouseCode == warehouseCode)
                .Select(w => w.WarehouseId) // 只查询需要的主键字段，优化性能
                .FirstOrDefaultAsync();

            // 处理查询结果（根据业务需求选择抛出异常或返回0）
            if (warehouse == 0)
            {
                throw new KeyNotFoundException($"未找到编号为{warehouseCode}的仓库信息");
                // 若不想抛出异常，也可返回0，由调用方处理：
                // return 0;
            }

            return warehouse;
        }

        /// <summary>
        /// 事务处理：同时保存仓库和库位信息
        /// </summary>
        public async Task<int> SaveWithTransactionAsync(BiWarehouse warehouse, List<WhLocation> locations)
        {
            using (var transaction = await _dbContext.Database.BeginTransactionAsync())
            {
                try
                {
                    // 保存仓库信息
                    if (string.IsNullOrEmpty(warehouse.WarehouseId.ToString()) || warehouse.WarehouseId == 0)
                    {
                        await _dbContext.BiWarehouse.AddAsync(warehouse);
                    }
                    else
                    {
                        _dbContext.BiWarehouse.Update(warehouse);
                    }
                    await _dbContext.SaveChangesAsync();

                    // 保存库位信息
                    foreach (var location in locations)
                    {
                        // 不使用外键关联，所以不需要设置仓库ID关联
                        if (string.IsNullOrEmpty(location.StorageLocationId.ToString()) || location.StorageLocationId == 0)
                        {
                            await _dbContext.WhLocation.AddAsync(location);
                        }
                        else
                        {
                            _dbContext.WhLocation.Update(location);
                        }
                    }
                    await _dbContext.SaveChangesAsync();

                    // 提交事务
                    await transaction.CommitAsync();
                    return 1;
                }
                catch (Exception)
                {
                    // 回滚事务
                    await transaction.RollbackAsync();
                    throw;
                }
            }
        }
        public async Task<List<UsersDTO>> GetWarehouseManagersAsync(long warehouseManageOrgId)
        {
            // 核心：仓库表的ManageOrgId 与 用户表的OrgId 关联
            // 即：通过仓库的"管理组织ID"，查询用户表中"所属部门ID"与之匹配的用户
            return await _dbContext.Set<Users>()
                // 筛选条件：用户的OrgId = 仓库的ManageOrgId（确保是同一部门）
                .Where(user => user.OrgId == warehouseManageOrgId
                            && !string.IsNullOrEmpty(user.UsersName)) // 排除无效用户
                .Select(user => new UsersDTO
                {
                    UsersId = (int)user.UsersId, // 用于绑定仓库的ManagerId
                    UsersName = user.UsersName, // 用户名
                    OrgId = (int)user.OrgId, // 部门ID（冗余，可用于校验）
                    //UsersHoneUmber = user.UsersHoneUmber // 手机号（核心：用于自动填充）
                })
                .OrderBy(user => user.UsersName)  // 按用户名排序
                .ToListAsync();
        }
    }
}
